<!DOCTYPE html>












  


<html class="theme-next muse use-motion" lang="zh-CN">
<head><meta name="generator" content="Hexo 3.8.0">
  <meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">












<meta http-equiv="Cache-Control" content="no-transform">
<meta http-equiv="Cache-Control" content="no-siteapp">






















<link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css">

<link href="/css/main.css?v=6.3.0" rel="stylesheet" type="text/css">


  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png?v=6.3.0">


  <link rel="icon" type="image/png" sizes="32x32" href="/favicon.ico?v=6.3.0">


  <link rel="icon" type="image/png" sizes="16x16" href="/favicon.ico?v=6.3.0">


  <link rel="mask-icon" href="/images/logo.svg?v=6.3.0" color="#222">









<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Muse',
    version: '6.3.0',
    sidebar: {"position":"left","display":"post","offset":12,"b2t":false,"scrollpercent":false,"onmobile":false},
    fancybox: false,
    fastclick: false,
    lazyload: false,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>


  




  <meta name="description" content="Based on Oracle Database 11gR2">
<meta name="keywords" content="Database,Oracle">
<meta property="og:type" content="article">
<meta property="og:title" content="Oracle Database常用SQL">
<meta property="og:url" content="https://yamdestiny.xyz/2018/03/04/Oracle-Database-Common-SQL/index.html">
<meta property="og:site_name" content="Yamdestiny">
<meta property="og:description" content="Based on Oracle Database 11gR2">
<meta property="og:locale" content="zh-CN">
<meta property="og:updated_time" content="2019-04-02T01:23:07.951Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Oracle Database常用SQL">
<meta name="twitter:description" content="Based on Oracle Database 11gR2">






  <link rel="canonical" href="https://yamdestiny.xyz/2018/03/04/Oracle-Database-Common-SQL/">



<script type="text/javascript" id="page.configurations">
  CONFIG.page = {
    sidebar: "",
  };
</script>

  <title>Oracle Database常用SQL | Yamdestiny</title>
  




<script async src="https://www.googletagmanager.com/gtag/js?id=UA-92396016-1"></script>
<script>
  window.dataLayer = window.dataLayer || [];
  function gtag(){dataLayer.push(arguments);}
  gtag('js', new Date());

  gtag('config', 'UA-92396016-1');
</script>



  <script type="text/javascript">
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "https://hm.baidu.com/hm.js?4eacbd1e85d8eebe356b663a83063d78";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>




  <noscript>
  <style type="text/css">
    .use-motion .motion-element,
    .use-motion .brand,
    .use-motion .menu-item,
    .sidebar-inner,
    .use-motion .post-block,
    .use-motion .pagination,
    .use-motion .comments,
    .use-motion .post-header,
    .use-motion .post-body,
    .use-motion .collection-title { opacity: initial; }

    .use-motion .logo,
    .use-motion .site-title,
    .use-motion .site-subtitle {
      opacity: initial;
      top: initial;
    }

    .use-motion {
      .logo-line-before i { left: initial; }
      .logo-line-after i { right: initial; }
    }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-CN">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/" class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">Yamdestiny</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
    
  </div>

  <div class="site-nav-toggle">
    <button aria-label="切换导航栏">
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>



<nav class="site-nav">
  
    <ul id="menu" class="menu">
      
        
        
        
          
          <li class="menu-item menu-item-home">
    <a href="/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-home"></i> <br>首页</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-tags">
    <a href="/tags/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-tags"></i> <br>标签</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-categories">
    <a href="/categories/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-th"></i> <br>分类</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-archives">
    <a href="/archives/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-archive"></i> <br>归档</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-series">
    <a href="/series/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-sitemap"></i> <br>系列</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-resource">
    <a href="/resource/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-file"></i> <br>资源</a>
  </li>

      
      
    </ul>
  

  
    

  

  
</nav>



  



</div>
    </header>

    
  
  
  
    
      
    
    <a href="https://github.com/YamDestiny" class="github-corner" target="_blank" title="Follow me on GitHub" aria-label="Follow me on GitHub"><svg width="80" height="80" viewbox="0 0 250 250" style="fill:#222; color:#fff; position: absolute; top: 0; border: 0; right: 0;" aria-hidden="true"><path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"/><path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2" fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"/><path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z" fill="currentColor" class="octo-body"/></svg>
    
      </a>
    



    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://yamdestiny.xyz/2018/03/04/Oracle-Database-Common-SQL/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="YamDestiny">
      <meta itemprop="description" content="以铜为镜，可以正衣冠；<br />以史为镜，可以知兴替；<br />以人为镜，可以明得失。">
      <meta itemprop="image" content="https://p3.music.126.net/Am30CzvY9NTxy1xiz5k3GQ==/7729566744520405.jpg?param=170y170">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Yamdestiny">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">Oracle Database常用SQL
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">

            
            
            

            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              

              
                
              

              <time title="创建时间：2018-03-04 11:29:28" itemprop="dateCreated datePublished" datetime="2018-03-04T11:29:28+08:00">2018-03-04</time>
            

            
              

              
                
                <span class="post-meta-divider">|</span>
                

                <span class="post-meta-item-icon">
                  <i class="fa fa-calendar-check-o"></i>
                </span>
                
                  <span class="post-meta-item-text">更新于</span>
                
                <time title="修改时间：2019-04-02 09:23:07" itemprop="dateModified" datetime="2019-04-02T09:23:07+08:00">2019-04-02</time>
              
            
          </span>

          
            <span class="post-category">
            
              <span class="post-meta-divider">|</span>
            
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              
                <span class="post-meta-item-text">分类于</span>
              
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing"><a href="/categories/衣带渐宽终不悔，为伊消得人憔悴/" itemprop="url" rel="index"><span itemprop="name">衣带渐宽终不悔，为伊消得人憔悴</span></a></span>

                
                
                  ，
                
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing"><a href="/categories/衣带渐宽终不悔，为伊消得人憔悴/Database/" itemprop="url" rel="index"><span itemprop="name">Database</span></a></span>

                
                
              
            </span>
          

          
            
              <span class="post-comments-count">
                <span class="post-meta-divider">|</span>
                <span class="post-meta-item-icon">
                  <i class="fa fa-comment-o"></i>
                </span>
                <a href="/2018/03/04/Oracle-Database-Common-SQL/#comments" itemprop="discussionUrl">
                
                  <span class="post-comments-count disqus-comment-count" data-disqus-identifier="2018/03/04/Oracle-Database-Common-SQL/" itemprop="commentCount"></span>
                </a>
              </span>
            
          

          
          
             <span id="/2018/03/04/Oracle-Database-Common-SQL/" class="leancloud_visitors" data-flag-title="Oracle Database常用SQL">
               <span class="post-meta-divider">|</span>
               <span class="post-meta-item-icon">
                 <i class="fa fa-eye"></i>
               </span>
               
                 <span class="post-meta-item-text">阅读次数：</span>
               
                 <span class="leancloud-visitors-count"></span>
             </span>
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <p>Based on Oracle Database 11gR2</p>
<a id="more"></a>
<h2 id="系统查询"><a href="#系统查询" class="headerlink" title="系统查询"></a>系统查询</h2><h3 id="查询系统视图或表"><a href="#查询系统视图或表" class="headerlink" title="查询系统视图或表"></a>查询系统视图或表</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- DBA/ALL/USER/V_$/GV_$/SESSION/INDEX开头的绝大部分都是视图</span></span><br><span class="line"><span class="comment">-- DBA_TABLES意为DBA拥有的或可以访问的所有的关系表。</span></span><br><span class="line"><span class="comment">-- ALL_TABLES意为某一用户拥有的或可以访问的所有的关系表。</span></span><br><span class="line"><span class="comment">-- USER_TABLES意为某一用户所拥有的所有的关系表。</span></span><br><span class="line"><span class="comment">-- 当某一用户本身就为数据库DBA时，DBA_TABLES与ALL_TABLES等价。</span></span><br><span class="line"><span class="comment">-- DBA_TABLES &gt;= ALL_TABLES &gt;= USER_TABLES</span></span><br><span class="line"><span class="comment">-- 需要注意的是在ORACLE数据库中大小写是敏感的，而此三表中数据默认都是大写的，所以在进行查询的时候注意小写的数据可能会造成数据无法查到。</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_VIEWS <span class="keyword">WHERE</span> VIEW_NAME <span class="keyword">LIKE</span> <span class="string">'DBA%'</span>;</span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_VIEWS <span class="keyword">WHERE</span> VIEW_NAME <span class="keyword">LIKE</span> <span class="string">'ALL%'</span>;</span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_VIEWS <span class="keyword">WHERE</span> VIEW_NAME <span class="keyword">LIKE</span> <span class="string">'USER%'</span>;</span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_VIEWS <span class="keyword">WHERE</span> VIEW_NAME <span class="keyword">LIKE</span> <span class="string">'V_$%'</span>;        <span class="comment">-- 针对某个实例的视图</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_VIEWS <span class="keyword">WHERE</span> VIEW_NAME <span class="keyword">LIKE</span> <span class="string">'GV_$%'</span>;       <span class="comment">-- 全局视图，针对多个实例环境</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_VIEWS <span class="keyword">WHERE</span> VIEW_NAME <span class="keyword">LIKE</span> <span class="string">'SESSION%'</span>;</span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_VIEWS <span class="keyword">WHERE</span> VIEW_NAME <span class="keyword">LIKE</span> <span class="string">'INDEX%'</span>;</span><br></pre></td></tr></table></figure>
<h4 id="常用DBA开头的视图"><a href="#常用DBA开头的视图" class="headerlink" title="常用DBA开头的视图"></a>常用DBA开头的视图</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_USERS;            <span class="comment">-- 数据库用户信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_ROLES;            <span class="comment">-- 角色信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_SEGMENTS;         <span class="comment">-- 表段信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_EXTENTS;          <span class="comment">-- 数据区信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_OBJECTS;          <span class="comment">-- 数据库对象信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_LOBS;             <span class="comment">-- LOB数据信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_TABLESPACES;      <span class="comment">-- 数据库表空间信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_DATA_FILES;       <span class="comment">-- 数据文件设置信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_TEMP_FILES;       <span class="comment">-- 临时数据文件信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_ROLLBACK_SEGS;    <span class="comment">-- 回滚段信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_TS_QUOTAS;        <span class="comment">-- 用户表空间配额信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_FREE_SPACE;       <span class="comment">-- 数据库空闲空间信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_PROFILES;         <span class="comment">-- 数据库用户资源限制信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_SYS_PRIVS;        <span class="comment">-- 用户的系统权限信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_TAB_PRIVS;        <span class="comment">-- 用户具有的对象权限信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_COL_PRIVS;        <span class="comment">-- 用户具有的列对象权限信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_ROLE_PRIVS;       <span class="comment">-- 用户具有的角色信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_AUDIT_TRAIL;      <span class="comment">-- 审计跟踪记录信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_STMT_AUDIT_OPTS;  <span class="comment">-- 审计设置信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_AUDIT_OBJECT;     <span class="comment">-- 对象审计结果信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_AUDIT_SESSION;    <span class="comment">-- 会话审计结果信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> DBA_INDEXES;          <span class="comment">-- 用户模式的索引信息</span></span><br></pre></td></tr></table></figure>
<h4 id="常用ALL开头的视图"><a href="#常用ALL开头的视图" class="headerlink" title="常用ALL开头的视图"></a>常用ALL开头的视图</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> ALL_USERS;            <span class="comment">-- 数据库所有用户的信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> ALL_OBJECTS;          <span class="comment">-- 数据库所有的对象的信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> ALL_DEF_AUDIT_OPTS;   <span class="comment">-- 所有默认的审计设置信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> ALL_TABLES;           <span class="comment">-- 所有的表对象信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> ALL_INDEXES;          <span class="comment">-- 所有的数据库对象索引的信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> ALL_TAB_COMMENTS;     <span class="comment">-- 查询所有用户的表,视图等</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> ALL_COL_COMMENTS;     <span class="comment">-- 查询所有用户的表的列名和注释.</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> ALL_TAB_COLUMNS;      <span class="comment">-- 查询所有用户的表的列名等信息(详细但是没有备注)</span></span><br></pre></td></tr></table></figure>
<h4 id="常用USER开头的视图"><a href="#常用USER开头的视图" class="headerlink" title="常用USER开头的视图"></a>常用USER开头的视图</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_OBJECTS;                     <span class="comment">-- 用户对象信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_SOURCE;                      <span class="comment">-- 数据库用户的所有资源对象信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_SEGMENTS;                    <span class="comment">-- 用户的表段信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_TABLES;                      <span class="comment">-- 用户的表对象信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_TAB_COLUMNS;                 <span class="comment">-- 用户的表列信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_CONSTRAINTS;                 <span class="comment">-- 用户的对象约束信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_SYS_PRIVS;                   <span class="comment">-- 当前用户的系统权限信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_TAB_PRIVS;                   <span class="comment">-- 当前用户的对象权限信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_COL_PRIVS;                   <span class="comment">-- 当前用户的表列权限信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_COL_COMMENTS;                <span class="comment">-- 查询本用户的表的列名和注释</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_ROLE_PRIVS;                  <span class="comment">-- 当前用户的角色权限信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_INDEXES;                     <span class="comment">-- 用户的索引信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_IND_COLUMNS;                 <span class="comment">-- 用户的索引对应的表列信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_CONS_COLUMNS;                <span class="comment">-- 用户的约束对应的表列信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_CLUSTERS;                    <span class="comment">-- 用户的所有簇信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_CLU_COLUMNS;                 <span class="comment">-- 用户的簇所包含的内容信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_CLUSTER_HASH_EXPRESSIONS;    <span class="comment">-- 散列簇的信息</span></span><br></pre></td></tr></table></figure>
<h4 id="常用V-开头的视图"><a href="#常用V-开头的视图" class="headerlink" title="常用V$开头的视图"></a>常用V$开头的视图</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$<span class="keyword">VERSION</span>;                        <span class="comment">-- 版本信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$<span class="keyword">DATABASE</span>;                       <span class="comment">-- 数据库信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$<span class="keyword">DATAFILE</span>;                       <span class="comment">-- 数据文件信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$<span class="keyword">CONTROLFILE</span>;                    <span class="comment">-- 控制文件信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$<span class="keyword">LOGFILE</span>;                        <span class="comment">-- 重做日志信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$<span class="keyword">INSTANCE</span>;                       <span class="comment">-- 数据库实例信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$<span class="keyword">LOG</span>;                            <span class="comment">-- 日志组信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$LOGHIST;                        <span class="comment">-- 日志历史信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$SGA;                            <span class="comment">-- 数据库SGA信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$PARAMETER;                      <span class="comment">-- 初始化参数信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$PROCESS;                        <span class="comment">-- 数据库服务器进程信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$BGPROCESS;                      <span class="comment">-- 数据库后台进程信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$CONTROLFILE_RECORD_SECTION;     <span class="comment">-- 控制文件记载的各部分信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$<span class="keyword">THREAD</span>;                         <span class="comment">-- 线程信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$DATAFILE_HEADER;                <span class="comment">-- 数据文件头所记载的信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$ARCHIVED_LOG;                   <span class="comment">-- 归档日志信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$ARCHIVE_DEST;                   <span class="comment">-- 归档日志的设置信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$LOGMNR_CONTENTS;                <span class="comment">-- 归档日志分析的DML DDL结果信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$LOGMNR_DICTIONARY;              <span class="comment">-- 日志分析的字典文件信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$LOGMNR_LOGS;                    <span class="comment">-- 日志分析的日志列表信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$<span class="keyword">TABLESPACE</span>;                     <span class="comment">-- 表空间信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$TEMPFILE;                       <span class="comment">-- 临时文件信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$FILESTAT;                       <span class="comment">-- 数据文件的I/O统计信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$UNDOSTAT;                       <span class="comment">-- UNDO数据信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$ROLLNAME;                       <span class="comment">-- 在线回滚段信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$<span class="keyword">SESSION</span>;                        <span class="comment">-- 会话信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$<span class="keyword">TRANSACTION</span>;                    <span class="comment">-- 事务信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$ROLLSTAT;                       <span class="comment">-- 回滚段统计信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$PWFILE_USERS;                   <span class="comment">-- 特权用户信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$SQLAREA;                        <span class="comment">-- 当前查询过的SQL语句访问过的资源及相关的信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$<span class="keyword">SQL</span>;                            <span class="comment">-- 与V$SQLAREA基本相同的相关信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> V$SYSSTAT;                        <span class="comment">-- 数据库系统状态信息</span></span><br></pre></td></tr></table></figure>
<h4 id="常用SESSION开头的视图"><a href="#常用SESSION开头的视图" class="headerlink" title="常用SESSION开头的视图"></a>常用SESSION开头的视图</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> SESSION_ROLES;    <span class="comment">-- 会话的角色信息</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> SESSION_PRIVS;    <span class="comment">-- 会话的权限信息</span></span><br></pre></td></tr></table></figure>
<h4 id="常用INDEX开头的视图"><a href="#常用INDEX开头的视图" class="headerlink" title="常用INDEX开头的视图"></a>常用INDEX开头的视图</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> INDEX_STATS;      <span class="comment">--索引的设置和存储信息</span></span><br></pre></td></tr></table></figure>
<h3 id="系统参数"><a href="#系统参数" class="headerlink" title="系统参数"></a>系统参数</h3><h4 id="数据库名"><a href="#数据库名" class="headerlink" title="数据库名"></a>数据库名</h4><ul>
<li>查看状态：<code>SHOW PARAMETER GLOBAL_NAME;</code></li>
<li>查值：<code>SELECT * FROM GLOBAL_NAME</code>;</li>
<li>改值：  <figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">ALTER SYSTEM SET GLOBAL_NAMES = <span class="literal">true</span>;</span><br><span class="line">ALTER DATABASE RENAME GLOBAL_NAME TO orcl;</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h4 id="节省空间的参数"><a href="#节省空间的参数" class="headerlink" title="节省空间的参数"></a>节省空间的参数</h4><p><code>deferred_segment_creation</code>是<code>11.2.0.4</code>版本的Oracle数据库新增参数，默认是true。这会导致在exp时，没有segment的对象不会导出。即空表不会导出。</p>
<ul>
<li>查看状态：<code>SHOW PARAMETER DEFERRED_SEGMENT_CREATION;</code></li>
<li>改值：<code>ALTER system SET deferred_segment_creation = false;</code>，关闭该功能，只对之后导入的表有效。</li>
</ul>
<h2 id="TABLESPACE-表空间"><a href="#TABLESPACE-表空间" class="headerlink" title="TABLESPACE 表空间"></a>TABLESPACE 表空间</h2><ul>
<li>查询表空间存储路径：<code>SELECT * FROM dba_data_files;</code></li>
</ul>
<blockquote>
<p><a href="http://blog.csdn.net/jmilk/article/details/51599260" target="_blank" rel="noopener">Oracle 表空间详解</a></p>
</blockquote>
<h3 id="创建表空间"><a href="#创建表空间" class="headerlink" title="创建表空间"></a>创建表空间</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TEMPORARY</span> <span class="keyword">TABLESPACE</span> TEST_TEMP TEMPFILE <span class="string">'C:\APP\USERNAME\ORADATA\LOCAL\TEST_TEMP01.DBF'</span> <span class="keyword">SIZE</span> <span class="number">1024</span>M <span class="keyword">AUTOEXTEND</span> <span class="keyword">ON</span> <span class="keyword">NEXT</span> <span class="number">32</span>M <span class="keyword">MAXSIZE</span> <span class="keyword">unlimited</span> <span class="keyword">EXTENT</span> <span class="keyword">MANAGEMENT</span> <span class="keyword">LOCAL</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLESPACE</span> TEST_DATA <span class="keyword">LOGGING</span> <span class="keyword">DATAFILE</span> <span class="string">'C:\APP\USERNAME\ORADATA\LOCAL\TEST_DATA01.DBF'</span> <span class="keyword">SIZE</span> <span class="number">1024</span>M <span class="keyword">AUTOEXTEND</span> <span class="keyword">ON</span> <span class="keyword">NEXT</span> <span class="number">32</span>M <span class="keyword">MAXSIZE</span> <span class="keyword">unlimited</span> <span class="keyword">EXTENT</span> <span class="keyword">MANAGEMENT</span> <span class="keyword">LOCAL</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLESPACE</span> TEST_SORT_DATA <span class="keyword">LOGGING</span> <span class="keyword">DATAFILE</span> <span class="string">'C:\APP\USERNAME\ORADATA\LOCAL\TEST_SORT_DATA01.DBF'</span> <span class="keyword">SIZE</span> <span class="number">1024</span>M <span class="keyword">AUTOEXTEND</span> <span class="keyword">ON</span> <span class="keyword">NEXT</span> <span class="number">32</span>M <span class="keyword">MAXSIZE</span> <span class="number">2048</span>M <span class="keyword">EXTENT</span> <span class="keyword">MANAGEMENT</span> <span class="keyword">LOCAL</span>;</span><br></pre></td></tr></table></figure>
<ul>
<li><code>TEMPORARY</code>，表示临时表空间，而且使用数据文件类型需设置为<code>TEMPFILE</code>。</li>
<li><code>TEMPFILE | DATAFILE</code>，表示存储的数据文件的类型。</li>
<li><code>SIZE</code>，表示数据文件的初始大小</li>
<li><code>AUTOEXTEND ON NEXT 32M</code>，表示自增长，当初始大小不够时，会自增长32M</li>
<li><code>MAXSIZE [unlimited | 2048M]</code>，表示自增长到不限制或限制为2048M</li>
</ul>
<h3 id="修改表空间"><a href="#修改表空间" class="headerlink" title="修改表空间"></a>修改表空间</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">USER</span> username defalut|<span class="keyword">temporary</span> <span class="keyword">TABLESPACE</span> tablespace_name;</span><br></pre></td></tr></table></figure>
<h3 id="删除表空间"><a href="#删除表空间" class="headerlink" title="删除表空间"></a>删除表空间</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 删除空的表空间，但是不包含物理文件</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">tablespace</span> tablespace_name;</span><br><span class="line"><span class="comment">-- 删除非空表空间，但是不包含物理文件</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">tablespace</span> tablespace_name <span class="keyword">including</span> <span class="keyword">contents</span>;</span><br><span class="line"><span class="comment">-- 删除空表空间，包含物理文件</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">tablespace</span> tablespace_name <span class="keyword">including</span> <span class="keyword">datafiles</span>;</span><br><span class="line"><span class="comment">-- 删除非空表空间，包含物理文件</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">tablespace</span> tablespace_name <span class="keyword">including</span> <span class="keyword">contents</span> <span class="keyword">and</span> <span class="keyword">datafiles</span>;</span><br><span class="line"><span class="comment">-- 如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">tablespace</span> tablespace_name <span class="keyword">including</span> <span class="keyword">contents</span> <span class="keyword">and</span> <span class="keyword">datafiles</span> <span class="keyword">CASCADE</span> <span class="keyword">CONSTRAINTS</span>;</span><br></pre></td></tr></table></figure>
<h2 id="USER-amp-ROLE-用户-amp-赋权"><a href="#USER-amp-ROLE-用户-amp-赋权" class="headerlink" title="USER&amp;ROLE 用户&amp;赋权"></a>USER&amp;ROLE 用户&amp;赋权</h2><h3 id="创建用户"><a href="#创建用户" class="headerlink" title="创建用户"></a>创建用户</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- Create the user </span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">user</span> <span class="keyword">test</span></span><br><span class="line">  <span class="keyword">identified</span> <span class="keyword">by</span> <span class="keyword">test</span></span><br><span class="line">  <span class="keyword">default</span> <span class="keyword">tablespace</span> TEST_DATA</span><br><span class="line">  <span class="keyword">temporary</span> <span class="keyword">tablespace</span> TEST_TEMP</span><br><span class="line">  profile <span class="keyword">DEFAULT</span>;</span><br><span class="line"><span class="comment">-- Grant/Revoke role privileges </span></span><br><span class="line"><span class="keyword">grant</span> <span class="keyword">connect</span> <span class="keyword">to</span> <span class="keyword">test</span>;</span><br><span class="line"><span class="keyword">grant</span> dba <span class="keyword">to</span> <span class="keyword">test</span>;</span><br><span class="line"><span class="comment">-- Grant/Revoke system privileges </span></span><br><span class="line"><span class="keyword">grant</span> <span class="keyword">unlimited</span> <span class="keyword">tablespace</span> <span class="keyword">to</span> <span class="keyword">test</span>;</span><br></pre></td></tr></table></figure>
<h3 id="删除用户"><a href="#删除用户" class="headerlink" title="删除用户"></a>删除用户</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">user</span> <span class="keyword">test</span> <span class="keyword">cascade</span></span><br></pre></td></tr></table></figure>
<h2 id="DBLink-数据库链接"><a href="#DBLink-数据库链接" class="headerlink" title="DBLink 数据库链接"></a>DBLink 数据库链接</h2><h3 id="查看DBLink"><a href="#查看DBLink" class="headerlink" title="查看DBLink"></a>查看DBLink</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> owner,object_name <span class="keyword">from</span> dba_objects <span class="keyword">where</span> object_type=<span class="string">'DATABASE LINK'</span>;</span><br><span class="line"><span class="comment">-- 或者</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> dba_db_links;</span><br></pre></td></tr></table></figure>
<h3 id="创建DBLink"><a href="#创建DBLink" class="headerlink" title="创建DBLink"></a>创建DBLink</h3><ul>
<li>查看用户是否有权限</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> user_sys_privs t <span class="keyword">where</span> t.privilege <span class="keyword">like</span> <span class="keyword">upper</span>(<span class="string">'%link%'</span>);</span><br></pre></td></tr></table></figure>
<ul>
<li>所创建的dblink只能是创建者能使用，别的用户使用不了<br>  <code>SYS CREATE DATABASE LINK NO</code></li>
<li>删除权限<br>  <code>SYS DROP PUBLIC DATABASE LINK NO</code></li>
<li>public表示所创建的dblink所有用户都可以使用<br>  <code>SYS CREATE PUBLIC DATABASE LINK NO</code></li>
<li><p>如没有权限</p>
  <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- sys用户下</span></span><br><span class="line"><span class="keyword">grant</span> <span class="keyword">CREATE</span> <span class="keyword">PUBLIC</span> <span class="keyword">DATABASE</span> <span class="keyword">LINK</span>，<span class="keyword">DROP</span> <span class="keyword">PUBLIC</span> <span class="keyword">DATABASE</span> <span class="keyword">LINK</span> <span class="keyword">to</span> <span class="keyword">test</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>创建</p>
  <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 没有配置tnsnames.ora</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">public</span> <span class="keyword">database</span> <span class="keyword">link</span> dblinkname </span><br><span class="line"><span class="keyword">connect</span> <span class="keyword">to</span> username </span><br><span class="line"><span class="keyword">identified</span> <span class="keyword">by</span> <span class="keyword">password</span></span><br><span class="line">　　 <span class="keyword">using</span> <span class="string">'(DESCRIPTION =</span></span><br><span class="line"><span class="string">(ADDRESS_LIST =</span></span><br><span class="line"><span class="string">(ADDRESS = (PROTOCOL = TCP)(HOST = database_ip)(PORT = 1521))</span></span><br><span class="line"><span class="string">)</span></span><br><span class="line"><span class="string">(CONNECT_DATA =</span></span><br><span class="line"><span class="string">(SERVICE_NAME =servicename)</span></span><br><span class="line"><span class="string">)</span></span><br><span class="line"><span class="string">)'</span>;</span><br></pre></td></tr></table></figure>
  <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 已配置tnsnames.ora</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">public</span> <span class="keyword">database</span> <span class="keyword">link</span> dblinktest </span><br><span class="line"><span class="keyword">connect</span> <span class="keyword">to</span> username </span><br><span class="line"><span class="keyword">identified</span> <span class="keyword">by</span> <span class="keyword">password</span> </span><br><span class="line"><span class="keyword">using</span> <span class="string">'test'</span>;</span><br><span class="line"><span class="comment">-- `test`即为tnsnames.ora中的 test = (DESCRIPTION = .....</span></span><br></pre></td></tr></table></figure>
</li>
</ul>
<h3 id="删除DBLink"><a href="#删除DBLink" class="headerlink" title="删除DBLink"></a>删除DBLink</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">public</span> <span class="keyword">database</span> <span class="keyword">link</span> dblinkname;</span><br></pre></td></tr></table></figure>
<h2 id="常用查询"><a href="#常用查询" class="headerlink" title="常用查询"></a>常用查询</h2><h3 id="ROWNUM"><a href="#ROWNUM" class="headerlink" title="ROWNUM"></a>ROWNUM</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- ROWNUM小于某个数时可以直接作为查询条件（注意ORACLE不支持SELECT TOP）</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> <span class="keyword">table</span> <span class="keyword">WHERE</span> <span class="keyword">ROWNUM</span> &lt; <span class="number">3</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询ROWNUM大于某个数值,需要使用子查询，并且ROWNUM需要有别名</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">ROWNUM</span> RN ,<span class="keyword">ID</span>,<span class="keyword">NAME</span> <span class="keyword">FROM</span> <span class="keyword">table</span>) <span class="keyword">WHERE</span> RN &gt; <span class="number">2</span>;</span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">ROWNUM</span> RN, table.* <span class="keyword">FROM</span> <span class="keyword">table</span>) <span class="keyword">WHERE</span> RN &gt; <span class="number">3</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 区间查询</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">ROWNUM</span> RN, table.* <span class="keyword">FROM</span> <span class="keyword">table</span>) <span class="keyword">WHERE</span> RN &gt; <span class="number">3</span> <span class="keyword">AND</span> RN &lt; <span class="number">6</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 排序+前N条</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">ROWNUM</span> RN, T.* <span class="keyword">FROM</span> ( <span class="keyword">SELECT</span> D.* <span class="keyword">FROM</span> <span class="keyword">table</span> D <span class="keyword">ORDER</span> <span class="keyword">BY</span> <span class="keyword">column</span>) T ) P <span class="keyword">WHERE</span> P.RN &lt; <span class="number">10</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 排序+区间查询1（效率低）</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">ROWNUM</span> RN, T.* <span class="keyword">FROM</span> ( <span class="keyword">SELECT</span> D.* <span class="keyword">FROM</span> <span class="keyword">table</span> D <span class="keyword">ORDER</span> <span class="keyword">BY</span> table_column)T ) P <span class="keyword">WHERE</span> P.RN &lt; <span class="number">9</span> <span class="keyword">AND</span> P.RN &gt; <span class="number">6</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 排序+区间查询2（效率高）</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">ROWNUM</span> RN, T.* <span class="keyword">FROM</span> ( <span class="keyword">SELECT</span> D.* <span class="keyword">FROM</span> <span class="keyword">table</span> D <span class="keyword">ORDER</span> <span class="keyword">BY</span> table_column) T <span class="keyword">WHERE</span> <span class="keyword">ROWNUM</span> &lt; <span class="number">9</span> ) P <span class="keyword">WHERE</span> P.RN &gt; <span class="number">6</span>;</span><br></pre></td></tr></table></figure>
<h3 id="分页"><a href="#分页" class="headerlink" title="分页"></a>分页</h3><h4 id="不排序"><a href="#不排序" class="headerlink" title="不排序"></a>不排序</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 效率低</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">ROWNUM</span> RN, D.* <span class="keyword">FROM</span> <span class="keyword">table</span> D ) P <span class="keyword">WHERE</span> P.RN &lt;= <span class="number">20</span> <span class="keyword">AND</span> P.RN &gt;= <span class="number">10</span>;</span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">ROWNUM</span> RN, D.* <span class="keyword">FROM</span> <span class="keyword">table</span> D ) P <span class="keyword">WHERE</span> P.RN <span class="keyword">BETWEEN</span> <span class="number">10</span> <span class="keyword">AND</span> <span class="number">20</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 效率高</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">ROWNUM</span> RN, D.* <span class="keyword">FROM</span> <span class="keyword">table</span> D <span class="keyword">WHERE</span> <span class="keyword">ROWNUM</span> &lt;= <span class="number">20</span> ) P <span class="keyword">WHERE</span> P.RN &gt;= <span class="number">10</span>;</span><br></pre></td></tr></table></figure>
<h4 id="排序"><a href="#排序" class="headerlink" title="排序"></a>排序</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--排序+区间查询1（效率低）</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">ROWNUM</span> RN, T.* <span class="keyword">FROM</span> ( <span class="keyword">SELECT</span> D.* <span class="keyword">FROM</span> <span class="keyword">table</span> D <span class="keyword">ORDER</span> <span class="keyword">BY</span> table_column) T ) P <span class="keyword">WHERE</span> P.RN &lt;= <span class="number">20</span> <span class="keyword">AND</span> P.RN &gt;= <span class="number">10</span>;</span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">ROWNUM</span> RN, T.* <span class="keyword">FROM</span> ( <span class="keyword">SELECT</span> D.* <span class="keyword">FROM</span> <span class="keyword">table</span> D <span class="keyword">ORDER</span> <span class="keyword">BY</span> table_column) T ) P <span class="keyword">WHERE</span> P.RN <span class="keyword">BETWEEN</span> <span class="number">10</span> <span class="keyword">AND</span> <span class="number">20</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">--排序+区间查询2（效率高）</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">ROWNUM</span> RN, T.* <span class="keyword">FROM</span> ( <span class="keyword">SELECT</span> D.* <span class="keyword">FROM</span> <span class="keyword">table</span> D <span class="keyword">ORDER</span> <span class="keyword">BY</span> table_column) T <span class="keyword">WHERE</span> <span class="keyword">ROWNUM</span> &lt;= <span class="number">20</span> ) P <span class="keyword">WHERE</span> P.RN &gt;= <span class="number">10</span>;</span><br></pre></td></tr></table></figure>
<h3 id="时间"><a href="#时间" class="headerlink" title="时间"></a>时间</h3><h4 id="TO-CHAR"><a href="#TO-CHAR" class="headerlink" title="TO_CHAR"></a>TO_CHAR</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> TO_CHAR(<span class="keyword">SYSDATE</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>) CURRENTTIME,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span>, <span class="string">'YYYY'</span>) <span class="keyword">YEAR</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span>, <span class="string">'MM'</span>) <span class="keyword">MONTH</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span>, <span class="string">'DD'</span>) <span class="keyword">DAY</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span>, <span class="string">'DAY'</span>) <span class="keyword">WEEK</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span>, <span class="string">'HH24'</span>) <span class="keyword">HOUR</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span>, <span class="string">'MI'</span>) <span class="keyword">MINUTE</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span>, <span class="string">'SS'</span>) <span class="keyword">SECOND</span></span><br><span class="line">  <span class="keyword">FROM</span> DUAL;</span><br></pre></td></tr></table></figure>
<h4 id="TO-DATE"><a href="#TO-DATE" class="headerlink" title="TO_DATE"></a>TO_DATE</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">TO_DATE</span>(<span class="string">'2009-07-04 05:02:01'</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>) CURRENTTIME,</span><br><span class="line">       TO_CHAR(<span class="keyword">TO_DATE</span>(<span class="string">'2009-07-04 05:02:01'</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>),</span><br><span class="line">               <span class="string">'YYYY'</span>) <span class="keyword">YEAR</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">TO_DATE</span>(<span class="string">'2009-07-04 05:02:01'</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>),</span><br><span class="line">               <span class="string">'MM'</span>) <span class="keyword">MONTH</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">TO_DATE</span>(<span class="string">'2009-07-04 05:02:01'</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>),</span><br><span class="line">               <span class="string">'DD'</span>) <span class="keyword">DAY</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">TO_DATE</span>(<span class="string">'2009-07-04 05:02:01'</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>),</span><br><span class="line">               <span class="string">'DAY'</span>) <span class="keyword">WEEK</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">TO_DATE</span>(<span class="string">'2009-07-04 05:02:01'</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>),</span><br><span class="line">               <span class="string">'DAY'</span>,</span><br><span class="line">               <span class="string">'NLS_DATE_LANGUAGE=AMERICAN'</span>) <span class="keyword">WEEK</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">TO_DATE</span>(<span class="string">'2009-07-04 05:02:01'</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>),</span><br><span class="line">               <span class="string">'HH24'</span>) <span class="keyword">HOUR</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">TO_DATE</span>(<span class="string">'2009-07-04 05:02:01'</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>),</span><br><span class="line">               <span class="string">'MI'</span>) <span class="keyword">MINUTE</span>,</span><br><span class="line">       TO_CHAR(<span class="keyword">TO_DATE</span>(<span class="string">'2009-07-04 05:02:01'</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>),</span><br><span class="line">               <span class="string">'SS'</span>) <span class="keyword">SECOND</span></span><br><span class="line">  <span class="keyword">FROM</span> DUAL;</span><br></pre></td></tr></table></figure>
<h4 id="MONTHS-BETWEEN"><a href="#MONTHS-BETWEEN" class="headerlink" title="MONTHS_BETWEEN"></a>MONTHS_BETWEEN</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> MONTHS_BETWEEN(<span class="keyword">TO_DATE</span>(<span class="string">'03-31-2014'</span>, <span class="string">'MM-DD-YYYY'</span>),</span><br><span class="line">                      <span class="keyword">TO_DATE</span>(<span class="string">'12-31-2013'</span>, <span class="string">'MM-DD-YYYY'</span>)) <span class="string">"MONTHS"</span></span><br><span class="line">  <span class="keyword">FROM</span> DUAL;</span><br></pre></td></tr></table></figure>
<h4 id="NEXT-DAY"><a href="#NEXT-DAY" class="headerlink" title="NEXT_DAY"></a>NEXT_DAY</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">SYSDATE</span> TODAY, NEXT_DAY(<span class="keyword">SYSDATE</span>, <span class="number">6</span>) NEXTWEEK <span class="keyword">FROM</span> DUAL;</span><br></pre></td></tr></table></figure>
<h4 id="INTERVAL"><a href="#INTERVAL" class="headerlink" title="INTERVAL"></a>INTERVAL</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> TO_CHAR(<span class="keyword">SYSDATE</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>) CURRENTTIME,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span> - <span class="built_in">INTERVAL</span> <span class="string">'7'</span> <span class="keyword">YEAR</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>) INTERVALYEAR,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span> - <span class="built_in">INTERVAL</span> <span class="string">'7'</span> <span class="keyword">MONTH</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>) INTERVALMONTH,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span> - <span class="built_in">INTERVAL</span> <span class="string">'7'</span> <span class="keyword">DAY</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>) INTERVALDAY,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span> - <span class="built_in">INTERVAL</span> <span class="string">'7'</span> <span class="keyword">HOUR</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>) INTERVALHOUR,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span> - <span class="built_in">INTERVAL</span> <span class="string">'7'</span> <span class="keyword">MINUTE</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>) INTERVALMINUTE,</span><br><span class="line">       TO_CHAR(<span class="keyword">SYSDATE</span> - <span class="built_in">INTERVAL</span> <span class="string">'7'</span> <span class="keyword">SECOND</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>) INTERVALSECOND</span><br><span class="line">  <span class="keyword">FROM</span> DUAL;</span><br></pre></td></tr></table></figure>
<h4 id="BETWEEN-AND"><a href="#BETWEEN-AND" class="headerlink" title="BETWEEN AND"></a>BETWEEN AND</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">id</span>, <span class="built_in">date</span></span><br><span class="line">  <span class="keyword">FROM</span> <span class="keyword">table</span></span><br><span class="line"> <span class="keyword">WHERE</span> <span class="keyword">TO_DATE</span>(<span class="built_in">date</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>) <span class="keyword">BETWEEN</span></span><br><span class="line">       <span class="keyword">TO_DATE</span>(<span class="string">'2014-02-01 00:00:00'</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>) <span class="keyword">AND</span></span><br><span class="line">       <span class="keyword">TO_DATE</span>(<span class="string">'2014-05-01 00:00:00'</span>, <span class="string">'YYYY-MM-DD HH24:MI:SS'</span>);</span><br></pre></td></tr></table></figure>
<h3 id="字符相关"><a href="#字符相关" class="headerlink" title="字符相关"></a>字符相关</h3><h4 id="长度"><a href="#长度" class="headerlink" title="长度"></a>长度</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">LENGTH</span>(<span class="string">'ABCDEFG'</span>) <span class="keyword">LEN</span>    <span class="comment">-- LENGTH </span></span><br><span class="line">  <span class="keyword">FROM</span> DUAL;</span><br></pre></td></tr></table></figure>
<h4 id="截取、查找、连接、替换"><a href="#截取、查找、连接、替换" class="headerlink" title="截取、查找、连接、替换"></a>截取、查找、连接、替换</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">SUBSTR</span>(<span class="string">'ABCDEFG'</span>, <span class="number">1</span>, <span class="number">5</span>) <span class="keyword">SUBSTR</span>,              <span class="comment">-- 字符串截取</span></span><br><span class="line">       <span class="keyword">INSTR</span>(<span class="string">'ABCDEFG'</span>, <span class="string">'BC'</span>) <span class="keyword">INSTR</span>,                <span class="comment">-- 查找子串</span></span><br><span class="line">       <span class="string">'HELLO'</span> || <span class="string">'WORLD'</span> <span class="keyword">CONCAT</span>,                   <span class="comment">-- 连接</span></span><br><span class="line">       <span class="keyword">CONCAT</span>(<span class="string">'11'</span>, <span class="string">'22'</span>) <span class="keyword">CONCAT</span>,　　　　　　　　      <span class="comment">-- 连接</span></span><br><span class="line">       <span class="keyword">REPLACE</span>(<span class="string">'WISH1'</span>, <span class="string">'1'</span>, <span class="string">'YOUHAPPY'</span>) <span class="keyword">REPLACE</span>    <span class="comment">-- 替换</span></span><br><span class="line">  <span class="keyword">FROM</span> DUAL;</span><br></pre></td></tr></table></figure>
<h4 id="去空格"><a href="#去空格" class="headerlink" title="去空格"></a>去空格</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">TRIM</span>(<span class="string">'  WISH  '</span>) <span class="keyword">TRIM</span>,   <span class="comment">-- 去前后空格</span></span><br><span class="line">       <span class="keyword">RTRIM</span>(<span class="string">'WISH  '</span>) <span class="keyword">RTRIM</span>,   <span class="comment">-- 去后面空格</span></span><br><span class="line">       <span class="keyword">LTRIM</span>(<span class="string">'  WISH'</span>) <span class="keyword">LTRIM</span>    <span class="comment">-- 去前面空格</span></span><br><span class="line">  <span class="keyword">FROM</span> DUAL;</span><br></pre></td></tr></table></figure>
<h4 id="去前后缀"><a href="#去前后缀" class="headerlink" title="去前后缀"></a>去前后缀</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">TRIM</span>(<span class="string">'W'</span> <span class="keyword">FROM</span> <span class="string">'WISH'</span>) TRIM1,</span><br><span class="line">       <span class="keyword">TRIM</span>(<span class="keyword">LEADING</span> <span class="string">'W'</span> <span class="keyword">FROM</span> <span class="string">'WISH'</span>) DELETEPREFIX,      <span class="comment">-- 去前缀</span></span><br><span class="line">       <span class="keyword">TRIM</span>(TRAILING <span class="string">'H'</span> <span class="keyword">FROM</span> <span class="string">'WISH'</span>) DELETETRAILING    <span class="comment">-- 去后缀</span></span><br><span class="line">  <span class="keyword">FROM</span> DUAL;</span><br></pre></td></tr></table></figure>
<h4 id="转码"><a href="#转码" class="headerlink" title="转码"></a>转码</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">ASCII</span>(<span class="string">'A'</span>) A1,</span><br><span class="line">       <span class="keyword">ASCII</span>(<span class="string">'A'</span>) A2,   <span class="comment">-- ASCII(转换为对应的十进制数)</span></span><br><span class="line">       <span class="keyword">CHR</span>(<span class="number">65</span>) C1,</span><br><span class="line">       <span class="keyword">CHR</span>(<span class="number">97</span>) C2       <span class="comment">-- CHR(十进制转对应字符)</span></span><br><span class="line">  <span class="keyword">FROM</span> DUAL;</span><br></pre></td></tr></table></figure>
<h4 id="大小写转换"><a href="#大小写转换" class="headerlink" title="大小写转换"></a>大小写转换</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">LOWER</span>(<span class="string">'WISH'</span>) <span class="keyword">LOWER</span>,</span><br><span class="line">       <span class="keyword">UPPER</span>(<span class="string">'WISH'</span>) <span class="keyword">UPPER</span>,</span><br><span class="line">       INITCAP(<span class="string">'WISH'</span>) INITCAP</span><br><span class="line">  <span class="keyword">FROM</span> DUAL;</span><br></pre></td></tr></table></figure>
<h4 id="转换"><a href="#转换" class="headerlink" title="转换"></a>转换</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">TRANSLATE</span>(<span class="string">'WISH1'</span>, <span class="string">'1'</span>, <span class="string">'Y'</span>) <span class="keyword">TRANSLATE</span>,      <span class="comment">-- 转换，对应一位(前面的位数大于等于后面的位数)</span></span><br><span class="line">       <span class="keyword">TRANSLATE</span>(<span class="string">'WISH1'</span>, <span class="string">'SH1'</span>, <span class="string">'HY'</span>) TRANSLATE1</span><br><span class="line">  <span class="keyword">FROM</span> DUAL;</span><br></pre></td></tr></table></figure>
<h2 id="参考文献"><a href="#参考文献" class="headerlink" title="参考文献"></a>参考文献</h2><ul>
<li><a href="https://www.cnblogs.com/jiangxinnju/p/5840420.html" target="_blank" rel="noopener">Oracle系统表整理+常用SQL语句收集</a></li>
<li><a href="https://www.cnblogs.com/wishyouhappy/p/3700683.html" target="_blank" rel="noopener">常用sql语句及案例（oracle）</a></li>
</ul>

      
    </div>

    

    
    
    

    

    

    
      <div>
        <ul class="post-copyright">
  <li class="post-copyright-author">
    <strong>本文作者： </strong>YamDestiny</li>
  <li class="post-copyright-link">
    <strong>本文链接：</strong>
    <a href="https://yamdestiny.xyz/2018/03/04/Oracle-Database-Common-SQL/" title="Oracle Database常用SQL">https://yamdestiny.xyz/2018/03/04/Oracle-Database-Common-SQL/</a>
  </li>
  <li class="post-copyright-license">
    <strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" rel="external nofollow" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明出处！</li>
</ul>

      </div>
    

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/Database/" rel="tag"># Database</a>
          
            <a href="/tags/Oracle/" rel="tag"># Oracle</a>
          
        </div>
      

      
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/2018/02/07/java-lang-Throwable/" rel="next" title="java.lang.Throwable">
                <i class="fa fa-chevron-left"></i> java.lang.Throwable
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/2018/04/03/docker-tutorial/" rel="prev" title="Docker 使用入门">
                Docker 使用入门 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>



    <div class="post-spread">
      
    </div>
  </div>


          </div>
          

  
    <div class="comments" id="comments">
      <div id="disqus_thread">
        <noscript>
          Please enable JavaScript to view the
          <a href="https://disqus.com/?ref_noscript">comments powered by Disqus.</a>
        </noscript>
      </div>
    </div>

  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview-wrap sidebar-panel">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <img class="site-author-image" itemprop="image" src="https://p3.music.126.net/Am30CzvY9NTxy1xiz5k3GQ==/7729566744520405.jpg?param=170y170" alt="YamDestiny">
            
              <p class="site-author-name" itemprop="name">YamDestiny</p>
              <p class="site-description motion-element" itemprop="description">以铜为镜，可以正衣冠；<br>以史为镜，可以知兴替；<br>以人为镜，可以明得失。</p>
          </div>

          
            <nav class="site-state motion-element">
              
                <div class="site-state-item site-state-posts">
                
                  <a href="/archives/">
                
                    <span class="site-state-item-count">49</span>
                    <span class="site-state-item-name">日志</span>
                  </a>
                </div>
              

              
                
                
                <div class="site-state-item site-state-categories">
                  <a href="/categories/index.html">
                    
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                    <span class="site-state-item-count">15</span>
                    <span class="site-state-item-name">分类</span>
                  </a>
                </div>
              

              
                
                
                <div class="site-state-item site-state-tags">
                  <a href="/tags/index.html">
                    
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                    <span class="site-state-item-count">45</span>
                    <span class="site-state-item-name">标签</span>
                  </a>
                </div>
              
            </nav>
          

          

          
            <div class="links-of-author motion-element">
              
                <span class="links-of-author-item">
                  <a href="https://github.com/YamDestiny" target="_blank" title="GitHub"><i class="fa fa-fw fa-github"></i>GitHub</a>
                  
                </span>
              
                <span class="links-of-author-item">
                  <a href="https://steamcommunity.com/id/maple_wqs" target="_blank" title="Steam"><i class="fa fa-fw fa-steam"></i>Steam</a>
                  
                </span>
              
            </div>
          

          
          

          
          

          
            
          
          

        </div>
      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#系统查询"><span class="nav-number">1.</span> <span class="nav-text">系统查询</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#查询系统视图或表"><span class="nav-number">1.1.</span> <span class="nav-text">查询系统视图或表</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#常用DBA开头的视图"><span class="nav-number">1.1.1.</span> <span class="nav-text">常用DBA开头的视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#常用ALL开头的视图"><span class="nav-number">1.1.2.</span> <span class="nav-text">常用ALL开头的视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#常用USER开头的视图"><span class="nav-number">1.1.3.</span> <span class="nav-text">常用USER开头的视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#常用V-开头的视图"><span class="nav-number">1.1.4.</span> <span class="nav-text">常用V$开头的视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#常用SESSION开头的视图"><span class="nav-number">1.1.5.</span> <span class="nav-text">常用SESSION开头的视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#常用INDEX开头的视图"><span class="nav-number">1.1.6.</span> <span class="nav-text">常用INDEX开头的视图</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#系统参数"><span class="nav-number">1.2.</span> <span class="nav-text">系统参数</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#数据库名"><span class="nav-number">1.2.1.</span> <span class="nav-text">数据库名</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#节省空间的参数"><span class="nav-number">1.2.2.</span> <span class="nav-text">节省空间的参数</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#TABLESPACE-表空间"><span class="nav-number">2.</span> <span class="nav-text">TABLESPACE 表空间</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#创建表空间"><span class="nav-number">2.1.</span> <span class="nav-text">创建表空间</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#修改表空间"><span class="nav-number">2.2.</span> <span class="nav-text">修改表空间</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#删除表空间"><span class="nav-number">2.3.</span> <span class="nav-text">删除表空间</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#USER-amp-ROLE-用户-amp-赋权"><span class="nav-number">3.</span> <span class="nav-text">USER&amp;ROLE 用户&amp;赋权</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#创建用户"><span class="nav-number">3.1.</span> <span class="nav-text">创建用户</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#删除用户"><span class="nav-number">3.2.</span> <span class="nav-text">删除用户</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#DBLink-数据库链接"><span class="nav-number">4.</span> <span class="nav-text">DBLink 数据库链接</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#查看DBLink"><span class="nav-number">4.1.</span> <span class="nav-text">查看DBLink</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#创建DBLink"><span class="nav-number">4.2.</span> <span class="nav-text">创建DBLink</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#删除DBLink"><span class="nav-number">4.3.</span> <span class="nav-text">删除DBLink</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#常用查询"><span class="nav-number">5.</span> <span class="nav-text">常用查询</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#ROWNUM"><span class="nav-number">5.1.</span> <span class="nav-text">ROWNUM</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#分页"><span class="nav-number">5.2.</span> <span class="nav-text">分页</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#不排序"><span class="nav-number">5.2.1.</span> <span class="nav-text">不排序</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#排序"><span class="nav-number">5.2.2.</span> <span class="nav-text">排序</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#时间"><span class="nav-number">5.3.</span> <span class="nav-text">时间</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#TO-CHAR"><span class="nav-number">5.3.1.</span> <span class="nav-text">TO_CHAR</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#TO-DATE"><span class="nav-number">5.3.2.</span> <span class="nav-text">TO_DATE</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#MONTHS-BETWEEN"><span class="nav-number">5.3.3.</span> <span class="nav-text">MONTHS_BETWEEN</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#NEXT-DAY"><span class="nav-number">5.3.4.</span> <span class="nav-text">NEXT_DAY</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#INTERVAL"><span class="nav-number">5.3.5.</span> <span class="nav-text">INTERVAL</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#BETWEEN-AND"><span class="nav-number">5.3.6.</span> <span class="nav-text">BETWEEN AND</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#字符相关"><span class="nav-number">5.4.</span> <span class="nav-text">字符相关</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#长度"><span class="nav-number">5.4.1.</span> <span class="nav-text">长度</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#截取、查找、连接、替换"><span class="nav-number">5.4.2.</span> <span class="nav-text">截取、查找、连接、替换</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#去空格"><span class="nav-number">5.4.3.</span> <span class="nav-text">去空格</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#去前后缀"><span class="nav-number">5.4.4.</span> <span class="nav-text">去前后缀</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#转码"><span class="nav-number">5.4.5.</span> <span class="nav-text">转码</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#大小写转换"><span class="nav-number">5.4.6.</span> <span class="nav-text">大小写转换</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#转换"><span class="nav-number">5.4.7.</span> <span class="nav-text">转换</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#参考文献"><span class="nav-number">6.</span> <span class="nav-text">参考文献</span></a></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright">&copy; 2017 &mdash; <span itemprop="copyrightYear">2019</span>
  <span class="with-love" id="animate">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">YamDestiny</span>

  

  
</div>




  <div class="powered-by">由 <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a> 强力驱动 v3.8.0</div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">主题 &mdash; <a class="theme-link" target="_blank" href="https://github.com/theme-next/hexo-theme-next">NexT.Muse</a> v6.3.0</div>




        








        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>


























  
  
    <script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  


  


  <script type="text/javascript" src="/js/src/utils.js?v=6.3.0"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=6.3.0"></script>



  
  

  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=6.3.0"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=6.3.0"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=6.3.0"></script>



  

  
    <script id="dsq-count-scr" src="https://yamdestiny.disqus.com/count.js" async></script>
  

  
    <script type="text/javascript">
      var disqus_config = function () {
        this.page.url = 'https://yamdestiny.xyz/2018/03/04/Oracle-Database-Common-SQL/';
        this.page.identifier = '2018/03/04/Oracle-Database-Common-SQL/';
        this.page.title = 'Oracle Database常用SQL';
        };
      function loadComments () {
        var d = document, s = d.createElement('script');
        s.src = 'https://yamdestiny.disqus.com/embed.js';
        s.setAttribute('data-timestamp', '' + +new Date());
        (d.head || d.body).appendChild(s);
      }
      
        $(function () {
          var offsetTop = $('#comments').offset().top - $(window).height();
          if (offsetTop <= 0) {
            // load directly when there's no a scrollbar
            loadComments();
          } else {
            $(window).on('scroll.disqus_scroll', function () {
              var scrollTop = document.documentElement.scrollTop;
              if (scrollTop >= offsetTop) {
                $(window).off('.disqus_scroll');
                loadComments();
              }
            });
          }
        });
      
    </script>
  





	





  












  





  

  
  <script src="https://cdn1.lncld.net/static/js/av-core-mini-0.6.4.js"></script>
  <script>AV.initialize("pc5KAuPRXg1dU4bG8nC957no-gzGzoHsz", "MSoOlaJ0XYd1YN1SbFBCENg2");</script>
  <script>
    function showTime(Counter) {
      var query = new AV.Query(Counter);
      var entries = [];
      var $visitors = $(".leancloud_visitors");

      $visitors.each(function () {
        entries.push( $(this).attr("id").trim() );
      });

      query.containedIn('url', entries);
      query.find()
        .done(function (results) {
          var COUNT_CONTAINER_REF = '.leancloud-visitors-count';

          if (results.length === 0) {
            $visitors.find(COUNT_CONTAINER_REF).text(0);
            return;
          }

          for (var i = 0; i < results.length; i++) {
            var item = results[i];
            var url = item.get('url');
            var time = item.get('time');
            var element = document.getElementById(url);

            $(element).find(COUNT_CONTAINER_REF).text(time);
          }
          for(var i = 0; i < entries.length; i++) {
            var url = entries[i];
            var element = document.getElementById(url);
            var countSpan = $(element).find(COUNT_CONTAINER_REF);
            if( countSpan.text() == '') {
              countSpan.text(0);
            }
          }
        })
        .fail(function (object, error) {
          console.log("Error: " + error.code + " " + error.message);
        });
    }

    function addCount(Counter) {
      var $visitors = $(".leancloud_visitors");
      var url = $visitors.attr('id').trim();
      var title = $visitors.attr('data-flag-title').trim();
      var query = new AV.Query(Counter);

      query.equalTo("url", url);
      query.find({
        success: function(results) {
          if (results.length > 0) {
            var counter = results[0];
            counter.fetchWhenSave(true);
            counter.increment("time");
            
            counter.save(null, {
              success: function(counter) {
                
                  var $element = $(document.getElementById(url));
                  $element.find('.leancloud-visitors-count').text(counter.get('time'));
                
              },
              error: function(counter, error) {
                console.log('Failed to save Visitor num, with error message: ' + error.message);
              }
            });
          } else {
            
              var newcounter = new Counter();
              /* Set ACL */
              var acl = new AV.ACL();
              acl.setPublicReadAccess(true);
              acl.setPublicWriteAccess(true);
              newcounter.setACL(acl);
              /* End Set ACL */
              newcounter.set("title", title);
              newcounter.set("url", url);
              newcounter.set("time", 1);
              newcounter.save(null, {
                success: function(newcounter) {
                  var $element = $(document.getElementById(url));
                  $element.find('.leancloud-visitors-count').text(newcounter.get('time'));
                },
                error: function(newcounter, error) {
                  console.log('Failed to create');
                }
              });
            
          }
        },
        error: function(error) {
          console.log('Error:' + error.code + " " + error.message);
        }
      });
    }

    $(function() {
      var Counter = AV.Object.extend("Counter");
      if ($('.leancloud_visitors').length == 1) {
        addCount(Counter);
      } else if ($('.post-title-link').length > 1) {
        showTime(Counter);
      }
    });
  </script>



  

  

  
  

  
  

  
    
      <script type="text/x-mathjax-config">
    MathJax.Hub.Config({
      tex2jax: {
        inlineMath: [ ['$','$'], ["\\(","\\)"]  ],
        processEscapes: true,
        skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
      },
      TeX: {equationNumbers: { autoNumber: "AMS" }}
    });
</script>

<script type="text/x-mathjax-config">
    MathJax.Hub.Queue(function() {
      var all = MathJax.Hub.getAllJax(), i;
        for (i=0; i < all.length; i += 1) {
          all[i].SourceElement().parentNode.className += ' has-jax';
        }
    });
</script>
<script type="text/javascript" src="//cdn.jsdelivr.net/npm/mathjax@2.7.1/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>

    
  


  
  

  

  

  

  

  

</body>
</html>
